package com.jeasonzhao.commons.db;

import java.beans.PropertyDescriptor;
import java.io.IOException;
import java.io.Reader;
import java.lang.reflect.InvocationTargetException;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;

import org.apache.commons.beanutils.PropertyUtils;
import com.jeasonzhao.commons.basic.StringCollection;
import com.jeasonzhao.commons.db.helper.ConnectionHelper;
import com.jeasonzhao.commons.parser.lex.LexException;
import com.jeasonzhao.commons.parser.lex.SqlTokenizer;
import com.jeasonzhao.commons.utils.Algorithms;
import com.jeasonzhao.commons.utils.ConvertEx;
import com.jeasonzhao.commons.utils.DataTypes;
import com.jeasonzhao.commons.json.JSONClass;

public class Sqlca
{
    private Connection connection = null;
    private ResultSet resultSet = null;
    private PreparedStatement statement = null;
    private SqlExecutor executor = null;
    private int sqlRows = 0;
    private boolean lastColumnIsNull = false;
    private ResultSetScrollType scrollType = ResultSetScrollType.TYPE_FORWARD_ONLY;
    private ResultSetCursorType cursorType = ResultSetCursorType.CONCUR_READ_ONLY;
    private boolean connectionOwner = false;
    private Sqlca()
    {
        connectionOwner = false;
    }

    public Sqlca(Connection connection)
    {
        this();
        this.connection = connection;
    }

    public Sqlca(String strDriver,String strConnectionString,String strUserName,String strPassword)
        throws SQLException
    {
        this();
        connection = ConnectionHelper.connect(strDriver,strConnectionString,strUserName,strPassword);
        this.connectionOwner = true;
    }

    public Sqlca(DataSource dataSource)
        throws SQLException
    {
        this();
        if(null == dataSource)
        {
            throw new SQLException("Could not bulid a instance without a connection.");
        }
        connection = dataSource.getConnection();
    }

    public Sqlca(DataSource dataSource,String strUser,String strPwd)
        throws SQLException
    {
        this();
        connection = dataSource.getConnection(strUser,strPwd);
    }

    protected void finalize()
        throws Throwable
    {
        this.close();
        super.finalize();
    }

    /**
     * Split one whole SQL statements into single SQLs, this method does:
     * 1. Remove all comments
     * 2. Split SQL statements by SQL92 and TSQL delimiters.
     * For example:
     * The input string "select a from b;insert a values(1) go select * from c;"
     * Three SQL statements could be splitter:
     * 1.select a from b
     * 2.insert a values(1)
     * 3.select * from c;
     * @param strSql String the initial SQL statement
     * @return StringCollection The spited SQL Statement. It will return null when
     * the initial SQL statement is NULL or empty (blank spaces would be removed automatically.)
     */
    public StringCollection splitSQL(String strSql)
    {
        if(Algorithms.isEmpty(strSql))
        {
            return null;
        }
        try
        {
            return SqlTokenizer.splitSQLStatement(strSql);
        }
        catch(LexException ex)
        {
            return null;
        }
    }

    /**
     * Set the scrollType and cursorType for the resultSet would be generated.
     * @param scrollType int Scroll Type
     * @param cursorType int Cursor Type
     * @see java.sql.ResultSet
     */
    public Sqlca setResultSetType(ResultSetScrollType scrollType,ResultSetCursorType cursorType)
    {
        this.scrollType = scrollType;
        this.cursorType = cursorType;
        return this;
    }

    public Sqlca resetResultSetType()
    {
        scrollType = ResultSetScrollType.TYPE_FORWARD_ONLY;
        cursorType = ResultSetCursorType.CONCUR_READ_ONLY;
        return this;
    }

    public Sqlca setConnection(Connection newConnection)
    {
        if(this.connectionOwner)
        {
            this.closeAll();
        }
        else
        {
            this.close(); //Close the existing opened cursor first.
        }
        this.connectionOwner = false;
        connection = newConnection;
        return this;
    }

    public Connection getConnection()
    {
        return connection;
    }

    public Sqlca setSql(String sqlStatement)
        throws SQLException
    {
        close();
        if(Algorithms.isEmpty(sqlStatement))
        {
            throw new SQLException("The SQL statement passed to SQLCA should not be null or empty.");
        }
        if(null == executor)
        {
            executor = new SqlExecutor(sqlStatement);
        }
        return this;
    }

    public Sqlca beginTransaction()
        throws SQLException
    {
        this.setAutoCommit(false);
        return this;
    }

    public Sqlca setAutoCommit(boolean bAuto)
        throws SQLException
    {
        if(connection == null)
        {
            throw new SQLException("No connection assigned to this SQLCA instance.");
        }
        try
        {
            connection.setAutoCommit(bAuto);
        }
        catch(SQLException excep)
        {
            throw new SQLException("Failed to set auto-commit flag," + excep.getMessage());
        }
        return this;
    }

    public String getString(int columnIndex)
        throws SQLException
    {
        String retValue = null;
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            retValue = resultSet.getString(columnIndex);
            lastColumnIsNull = resultSet.wasNull();
            if(null == retValue && lastColumnIsNull == false)
            {
                Object obj = resultSet.getObject(columnIndex);
                if(obj != null && obj instanceof java.sql.Clob)
                {
                    java.sql.Clob clob = (java.sql.Clob) obj;
                    retValue = clob2String(clob);
                }
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnIndex,excep);
        }
    }

    public String getString(String columnName)
        throws SQLException
    {
        String retValue = null;
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            retValue = resultSet.getString(columnName);
            lastColumnIsNull = resultSet.wasNull();
            if(null == retValue && lastColumnIsNull == false)
            {
                Object obj = resultSet.getObject(columnName);
                if(obj != null && obj instanceof java.sql.Clob)
                {
                    java.sql.Clob clob = (java.sql.Clob) obj;
                    retValue = clob2String(clob);
                }
            }
            return retValue;
        }
        catch(SQLException excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnName,excep);
        }
    }

    private String clob2String(Clob clob)
        throws SQLException
    {
        StringBuffer retValue = new StringBuffer();
        Reader reader = clob.getCharacterStream();
        int i;
        char ac[] = new char[1024];
        try
        {
            while((i = reader.read(ac,0,1024)) != -1)
            {
                retValue.append(new String(ac,0,i));
            }
        }
        catch(IOException ex)
        {
            throw new SQLException(ex.getMessage());
        }
        return retValue.toString();
    }

    public int getInt(int columnIndex)
        throws SQLException
    {
        int retValue = -1;
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            retValue = resultSet.getInt(columnIndex);
            lastColumnIsNull = resultSet.wasNull();
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnIndex,excep);
        }
    }

    public int getInt(String columnName)
        throws SQLException
    {
        int retValue;
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            retValue = resultSet.getInt(columnName);
            lastColumnIsNull = false;
            if(resultSet.wasNull())
            {
                lastColumnIsNull = true;
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnName,excep);
        }
    }

    public Integer getInteger(int columnIndex)
        throws SQLException
    {

        Integer retValue = null;
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            int tempVar = resultSet.getInt(columnIndex);
            lastColumnIsNull = false;
            if(resultSet.wasNull())
            {
                lastColumnIsNull = true;
                retValue = null;
            }
            else
            {
                retValue = Integer.valueOf(tempVar);
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnIndex,excep);
        }

    }

    public Integer getInteger(String columnName)
        throws SQLException
    {
        Integer retValue = null;
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            int tempVar = resultSet.getInt(columnName);
            lastColumnIsNull = false;
            if(resultSet.wasNull())
            {
                lastColumnIsNull = true;
                retValue = null;
            }
            else
            {
                retValue = Integer.valueOf(tempVar);
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnName,excep);
        }

    }

    public long getLong(int columnIndex)
        throws SQLException
    {

        long retValue = 0;
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            retValue = resultSet.getLong(columnIndex);
            lastColumnIsNull = false;
            if(resultSet.wasNull())
            {
                lastColumnIsNull = true;
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnIndex,excep);
        }

    }

    public long getLong(String columnName)
        throws SQLException
    {
        long retValue;
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            retValue = resultSet.getLong(columnName);
            lastColumnIsNull = false;
            if(resultSet.wasNull())
            {
                lastColumnIsNull = true;
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnName,excep);
        }

    }

    public double getDouble(int columnIndex)
        throws SQLException
    {

        double retValue = 0;
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            retValue = resultSet.getDouble(columnIndex);
            lastColumnIsNull = false;
            if(resultSet.wasNull())
            {
                lastColumnIsNull = true;
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnIndex,excep);
        }

    }

    public double getDouble(String columnName)
        throws SQLException
    {
        double retValue;
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            retValue = resultSet.getDouble(columnName);
            lastColumnIsNull = false;
            if(resultSet.wasNull())
            {
                lastColumnIsNull = true;
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnName,excep);
        }

    }

    public boolean getBoolean(int columnIndex)
        throws SQLException
    {

        boolean retValue = false;
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            retValue = resultSet.getBoolean(columnIndex);
            lastColumnIsNull = false;
            if(resultSet.wasNull())
            {
                lastColumnIsNull = true;
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnIndex,excep);
        }

    }

    public Object get(String columnName)
        throws SQLException
    {
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        return resultSet.getObject(columnName);
    }

    public Object get(int nColumnIndex)
        throws SQLException
    {
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        return resultSet.getObject(nColumnIndex);
    }

    public boolean getBoolean(String columnName)
        throws SQLException
    {
        boolean retValue = false;
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            retValue = resultSet.getBoolean(columnName);
            lastColumnIsNull = false;
            if(resultSet.wasNull())
            {
                lastColumnIsNull = true;
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnName,excep);
        }

    }

    public java.util.Date getDate(int columnIndex)
        throws SQLException
    {
        java.util.Date retValue = null;

        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            java.sql.Date tmp = resultSet.getDate(columnIndex);
            if(tmp != null)
            {
                retValue = new java.util.Date(tmp.getTime());
            }
            else
            {
                retValue = null;
            }
            lastColumnIsNull = false;
            if(resultSet.wasNull())
            {
                retValue = null;
                lastColumnIsNull = true;
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnIndex,excep);
        }

    }

    public String getDateString(String columnName)
        throws SQLException
    {
        java.util.Date date = getDate(columnName);
        return null == date ? "" : (new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss")).format(date);
    }

    public String getDateString(int columnName)
        throws SQLException
    {
        java.util.Date date = getDate(columnName);
        return null == date ? "" : (new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss")).format(date);
    }

    public java.util.Date getDate(String columnName)
        throws SQLException
    {
        java.util.Date retValue = null;
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            java.sql.Date tmp = resultSet.getDate(columnName);
            if(tmp != null)
            {
                retValue = new java.util.Date(tmp.getTime());
            }
            else
            {
                retValue = null;
            }
            lastColumnIsNull = false;
            if(resultSet.wasNull())
            {
                lastColumnIsNull = true;
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnName,excep);
        }

    }

    public java.sql.Timestamp getTimestamp(int columnIndex)
        throws SQLException
    {

        Timestamp retValue = null;
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            retValue = resultSet.getTimestamp(columnIndex);
            lastColumnIsNull = false;
            if(resultSet.wasNull())
            {
                retValue = null;
                lastColumnIsNull = true;
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnIndex,excep);
        }

    }

    public java.sql.Timestamp getTimestamp(String columnName)
        throws SQLException
    {
        Timestamp retValue = null;
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            retValue = resultSet.getTimestamp(columnName);
            lastColumnIsNull = false;
            if(resultSet.wasNull())
            {
                retValue = null;
                lastColumnIsNull = true;
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnName,excep);
        }

    }

    public float getFloat(int columnIndex)
        throws SQLException
    {

        float retValue;
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            retValue = resultSet.getFloat(columnIndex);
            lastColumnIsNull = false;
            if(resultSet.wasNull())
            {
                lastColumnIsNull = true;
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnIndex,excep);
        }

    }

    public Clob getClob(int columnIndex)
        throws SQLException
    {
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            Clob retValue = resultSet.getClob(columnIndex);
            lastColumnIsNull = false;
            if(resultSet.wasNull())
            {
                retValue = null;
                lastColumnIsNull = true;
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnIndex,excep);
        }

    }

    public Clob getClob(String columnIndex)
        throws SQLException
    {
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            Clob retValue = resultSet.getClob(columnIndex);
            lastColumnIsNull = false;
            if(resultSet.wasNull())
            {
                retValue = null;
                lastColumnIsNull = true;
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnIndex,excep);
        }

    }

    public float getFloat(String columnName)
        throws SQLException
    {

        float retValue;
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        try
        {
            retValue = resultSet.getFloat(columnName);
            lastColumnIsNull = false;
            if(resultSet.wasNull())
            {
                lastColumnIsNull = true;
            }
            return retValue;
        }
        catch(Exception excep)
        {
            throw new SqlcaExceptionColumnRetrieve(columnName,excep);
        }

    }

    public boolean next()
        throws SQLException
    {
        return resultSet.next();
    }

    public boolean isLastNull()
    {
        return lastColumnIsNull;
    }

    public void commit()
        throws SQLException
    {
        if(null != connection)
        {
            connection.commit();
        }
    }

    public void rollbackSafe()
    {
        try
        {
            rollback();
        }
        catch(Exception ex)
        {
        	ex.printStackTrace();
        }
    }

    public void rollback()
        throws SQLException
    {
        if(null != connection)
        {
            connection.rollback();
        }
    }

    public int getEffectRows()
    {
        return sqlRows;
    }

    public void closeAll()
    {
        close();
        try
        {
            if(null != connection)
            {
                connection.close();
            }
            connection = null;
        }
        catch(Exception ex)
        {
        	ex.printStackTrace();
        }
    }

    public void close()
    {
        sqlRows = -1;
        if(this.executor != null)
        {
            executor = null;
        }
        try
        {
            if(resultSet != null)
            {
                resultSet.close();
                resultSet = null;
            }
        }
        catch(SQLException ex)
        {
        	ex.printStackTrace();
        }
        try
        {
            if(statement != null)
            {
                statement.close();
                statement = null;
            }
        }
        catch(SQLException ex)
        {
        	ex.printStackTrace();
        }
    }

    public Sqlca set(int parameterIndex,Object obj)
        throws SQLException
    {
        return _set((Object) parameterIndex, -1,obj);
    }

    public Sqlca set(int parameterIndex,DataTypes dt,Object obj)
        throws SQLException
    {
        return _set((Object) parameterIndex, -1,null == dt ? obj : dt.castValue(obj));
    }

    public Sqlca set(int parameterIndex,int nType,Object obj)
        throws SQLException
    {
        return _set((Object) parameterIndex,nType,obj);
    }

    public Sqlca set(String parameterKey,Object obj)
        throws SQLException
    {
        return _set((Object) parameterKey, -1,obj);
    }

    public Sqlca set(String parameterKey,DataTypes dt,Object obj)
        throws SQLException
    {
        return _set((Object) parameterKey, -1,null == dt ? obj : dt.castValue(obj));
    }

    public Sqlca set(String parameterKey,int nType,Object obj)
        throws SQLException
    {
        return _set((Object) parameterKey,nType,obj);
    }

    private Sqlca _set(Object parameterKey,int nType,Object obj)
        throws SQLException
    {

        if(null == parameterKey)
        {
            return this;
        }
        if(null == executor)
        {
            throw new SqlcaExceptionNoSQL();
        }
        SqlParameter param = null;
        int nSQLType = nType < 0
            ? (null == obj ? java.sql.Types.VARCHAR : SqlTypesHelper.getSQLTypeFromClass(obj.getClass()))
            : nType;
        if(null != obj && obj.getClass().equals(java.util.Date.class))
        {
            obj = new java.sql.Timestamp(((java.util.Date) obj).getTime());
            nSQLType = java.sql.Types.TIMESTAMP;
        }
        if(DataTypes.isNumeric(parameterKey.getClass()))
        {
            param = new SqlParameter(ConvertEx.toInt(parameterKey));
        }
        else
        {
            param = new SqlParameter(Algorithms.toString(parameterKey));
        }
        if(null == obj)
        {
            param.setDbNull(nSQLType);
        }
        else
        {
            param.setValue(obj,nSQLType);
        }
        executor.setParameterValue(param);
        return this;
    }

    public java.sql.ResultSetMetaData getMetaData()
        throws SQLException
    {
        return null == resultSet ? null : resultSet.getMetaData();
    }

    public int getColumnCount()
        throws SQLException
    {
        java.sql.ResultSetMetaData m = this.getMetaData();
        return null == m ? 0 : m.getColumnCount();
    }
    public DataTypes getColumnType(int n)
        throws SQLException
    {
        java.sql.ResultSetMetaData m = this.getMetaData();
        return null == m ? DataTypes.STRING : SqlTypesHelper.getDataTypesFrom(m.getColumnType(n));
    }
    public DataTypes getColumnType(String name)
        throws SQLException
    {
        String[] ary=this.getColumnNames();
        int idx=-1;
        for(int n=0 ;null!=ary && n<ary.length;n++)
        {
            if(ary[n].equalsIgnoreCase(name))
            {
                idx=n+1;
                break;
            }
        }
        return idx<1?DataTypes.STRING :getColumnType(idx);
    }

    public java.util.Map<String,Object> getHashMap()
        throws SQLException
    {
        java.util.Map<String,Object> hash = new java.util.HashMap<String,Object>();
        String[] names = this.getColumnNames();
        for(String s : names)
        {
            hash.put(s,this.get(s));
        }
        return hash;
    }
    public JSONClass nextJSON()
        throws SQLException
    {
        if(this.next())
        {
            return getJSONObject();
        }
        else
        {
            return null;
        }
    }
    public JSONClass getJSONObject()
        throws SQLException
    {
        JSONClass cls=new JSONClass();
        String[] names=this.getColumnNames();
        for(String s: names)
        {
            cls.set(s,this.get(s));
        }
        return cls;
    }
    public String[] getColumnNames()
        throws SQLException
    {
        java.sql.ResultSetMetaData m = this.getMetaData();
        if(null == m)
        {
            return new String[0];
        }
        else
        {
	        int nColCount = m.getColumnCount();
	        String[] ret = new String[nColCount];
	        for(int n = 0;n < nColCount;n++)
	        {
	            ret[n] = m.getColumnName(n + 1);
	        }
	        return ret;
        }
    }

    public int getRowIndex()
        throws SQLException
    {
        return resultSet.getRow();
    }

    public boolean last()
        throws SQLException
    {
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        if(resultSet.getType() != ResultSet.TYPE_SCROLL_INSENSITIVE)
        {
            throw new SQLException("SQLCA Error: ResultSet.TYPE_SCROLL_INSENSITIVE has not been set");
        }
        return resultSet.last();
    }

    public boolean absolute(int nRowIndex)
        throws SQLException
    {
        if(nRowIndex < 1)
        {
            throw new SQLException("The rowindex must greater than 0.");
        }
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        if(resultSet.getType() != ResultSet.TYPE_SCROLL_INSENSITIVE)
        {
            throw new SQLException("SQLCA Error: ResultSet.TYPE_SCROLL_INSENSITIVE has not been set");
        }
        return resultSet.absolute(nRowIndex);
    }

    public boolean skipRows(int nRows2Skip)
        throws SQLException
    {
        if(nRows2Skip < 1)
        {
            return false;
        }
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        if(resultSet.getType() != ResultSet.TYPE_SCROLL_INSENSITIVE)
        {
            while(nRows2Skip > 0 && resultSet.next())
            {
                nRows2Skip--;
            }
            return true;
        }
        else
        {
            return resultSet.absolute(resultSet.getRow() + nRows2Skip);
        }
    }

    public static final void close(Sqlca sqlca)
    {
        if(null != sqlca)
        {
            sqlca.close();
        }
    }

    public static final void closeAll(Sqlca sqlca)
    {
        if(null != sqlca)
        {
            sqlca.closeAll();
        }
    }

    public static final void rollbackAndClose(Sqlca sqlca)
    {
        if(null != sqlca)
        {
            try
            {
                sqlca.rollback();
            }
            catch(SQLException ex)
            {
            	ex.printStackTrace();
            }
            sqlca.close();
        }
    }

    public static Sqlca fromResultSet(ResultSet resultSet)
    {
        Sqlca sqlca = new Sqlca();
        sqlca.resultSet = resultSet;
        return sqlca;
    }

    public int execute()
        throws SQLException
    {
        sqlRows = -1;
        if(this.connection == null)
        {
            throw new SQLException("No connection assigned to this SQLCA instance.");
        }
        if(this.executor == null)
        {
            throw new SqlcaExceptionNoSQL();
        }
        try
        {
            ExecutionResult result = executor.execute(this.connection,this.scrollType,this.cursorType);
            statement = result.getPrepareStatement();
            resultSet = result.getResultSet();
            sqlRows = result.getEffectRows();
            return sqlRows;
        }
        catch(SQLException ex)
        {
            throw new SQLException(this.executor.getExecutableSQL() + "," + ex.getMessage());
        }
    }

    public ResultSet getResultSet()
    {
        return resultSet;
    }

    public int execute(String strSql)
        throws SQLException
    {
        this.setSql(strSql);
        return execute();
    }

    public int execute(String strSql,Iterable<SqlParameter> allParameters)
        throws SQLException
    {
        setSql(strSql);
        if(null != allParameters)
        {
            for(java.util.Iterator<SqlParameter> it = allParameters.iterator();it.hasNext();)
            {
                executor.setParameterValue((SqlParameter) it.next());
            }
        }
        return execute();
    }

    public int execute(String strSql,SqlParameter ...allParameters)
        throws SQLException
    {
        setSql(strSql);
        setParameters(allParameters);
        return execute();
    }

    public int execute(String strSql,Object ...allParameters)
        throws SQLException
    {
        setSql(strSql);
        setParameters(allParameters);
        return execute();
    }

    public Sqlca setParameters(SqlParameter ...allParameters)
        throws SQLException
    {
        if(null == executor)
        {
            throw new SqlcaExceptionNoSQL();
        }
        for(int n = 0;null != allParameters && n < allParameters.length;n++)
        {
            SqlParameter pi = allParameters[n];
            SqlParameter p = null == pi
                ? SqlParameter.createDbNull(n + 1)
                : new SqlParameter(n + 1,pi.getSqlType(),pi.getValue());
            executor.setParameterValue(p);
        }
        return this;
    }

    public Sqlca setParameters(Object ...allParameters)
        throws SQLException
    {
        if(null == executor)
        {
            throw new SqlcaExceptionNoSQL();
        }
        for(int n = 0;null != allParameters && n < allParameters.length;n++)
        {
            executor.setParameterValue(SqlParameter.from(n + 1,allParameters[n]));
        }
        return this;
    }

    public Sqlca addBatch(Object ...allParameters)
        throws SQLException
    {
        if(null == executor)
        {
            throw new SqlcaExceptionNoSQL();
        }
        executor.addBatch();
        setParameters(allParameters);
        return this;
    }

    public Sqlca addBatch(SqlParameter ...allParameters)
        throws SQLException
    {
        if(null == executor)
        {
            throw new SqlcaExceptionNoSQL();
        }
        executor.addBatch();
        setParameters(allParameters);
        return this;
    }

    public int executeDiscardException(String strSql)
    {
        try
        {
            this.setSql(strSql);
            return execute();
        }
        catch(SQLException ex)
        {
            return -1;
        }
    }

    public int executeDiscardException()
    {
        try
        {
            return execute();
        }
        catch(SQLException ex)
        {
            return -1;
        }
    }

    public int executeDiscardException(String strSql,SqlParameter ...allParameters)
    {
        try
        {
            setSql(strSql);
            setParameters(allParameters);
            return execute();
        }
        catch(SQLException ex)
        {
            return -1;
        }
    }

    public int executeDiscardException(String strSql,Object ...allParameters)
    {
        try
        {
            setSql(strSql);
            setParameters(allParameters);
            return execute();
        }
        catch(SQLException ex)
        {
            return -1;
        }
    }

    public static class SqlcaExceptionNoResultSet extends SQLException
    {
        private static final long serialVersionUID = 1L;
        public SqlcaExceptionNoResultSet()
        {
            super("ResultSet has not been generated, it might be caused by no SQL statement or the SQL statement is not a SELECT statement.");
        }
    }

    public static class SqlcaExceptionNoSQL extends SQLException
    {
        private static final long serialVersionUID = 1L;

        public SqlcaExceptionNoSQL()
        {
            super("No SQL statement has been assigned to this SQLCA instance, use setSql(String statement) to assign");
        }
    }

    public static class SqlcaExceptionColumnRetrieve extends SQLException
    {
        private static final long serialVersionUID = 1L;
        public SqlcaExceptionColumnRetrieve(int nColumnIndex,Exception t)
        {
            super("Could not retrieve coulumn " + nColumnIndex + " value" +
                  (null == t ? "." : ("," + t.getMessage())));
        }

        public SqlcaExceptionColumnRetrieve(int nColumnIndex)
        {
            this(nColumnIndex,null);
        }

        public SqlcaExceptionColumnRetrieve(String columnName,Exception t)
        {
            super("Could not retrieve coulumn " + columnName + " value" +
                  (null == t ? "." : ("," + t.getMessage())));
        }

        public SqlcaExceptionColumnRetrieve(String columnName)
        {
            this(columnName,null);
        }
    }

    ////////////Simple Bean handler//////////
    public <T> List<T> queryBeans(Class<?> cls,String strSql,Object ...allParameters)
        throws SQLException
    {
        setSql(strSql);
        setParameters(allParameters);
        execute();
        return queryBeans(cls);
    }

    @SuppressWarnings("unchecked")
    public <T> List<T> queryBeans(Class<?> cls)
        throws SQLException
    {
        if(null == cls)
        {
            return null;
        }
        List<T> ary = new ArrayList<T>();
        while(this.next())
        {
            try
            {
                ary.add((T) fillBean(cls));
            }
            catch(Exception excep)
            {
                throw new SQLException(excep.getMessage());
            }
        }
        return ary;
    }

    @SuppressWarnings("unchecked")
    public <T> T fillBean(Class<?> cls)
        throws SQLException,IllegalAccessException,InstantiationException,InvocationTargetException,IllegalArgumentException,NoSuchMethodException
    {
        if(null == cls)
        {
            return null;
        }
        return(T) fillBean(cls.newInstance());
    }

    public Object fillBean(Object obj)
        throws SQLException,NoSuchMethodException,InvocationTargetException,IllegalAccessException
    {
        if(null == obj)
        {
            return null;
        }
        if(resultSet == null)
        {
            throw new SqlcaExceptionNoResultSet();
        }
        for(PropertyDescriptor p : PropertyUtils.getPropertyDescriptors(obj.getClass()))
        {
            if(null == p.getReadMethod() || null == p.getWriteMethod())
            {
                continue;
            }
            PropertyUtils.setSimpleProperty(obj,p.getName(),this.get(p.getName()));
        }
        return obj;
    }

    public int insertBeanInto(Object obj,String strTableName)
        throws SQLException
    {
        if(null == obj)
        {
            return 0;
        }
        if(Algorithms.isEmpty(strTableName))
        {
            throw new SQLException("When saving " + obj.getClass().getName() +
                                   " into database, the name of the table should not be empty");
        }
        if(this.connection == null)
        {
            throw new SQLException("No connection assigned to this SQLCA instance.");
        }
        StringBuffer strFields = new StringBuffer();
        StringBuffer strValues = new StringBuffer();
        List<String> fields = new ArrayList<String>();
        for(PropertyDescriptor p : PropertyUtils.getPropertyDescriptors(obj.getClass()))
        {
            if(null == p.getReadMethod() || null == p.getWriteMethod())
            {
                continue;
            }
            fields.add(p.getName());
            if(strFields.length() > 0)
            {
                strFields.append(',');
                strValues.append(',');
            }
            strFields.append(p.getName());
            strValues.append("@" + p.getName());
        }
        String strSql = "INSERT INTO " + strTableName + "(" + strFields.toString()
            + ") VALUES(" + strValues.toString() + ")";
        this.setSql(strSql);
        for(String strPname : fields)
        {
            try
            {
                this.set("@" + strPname,PropertyUtils.getSimpleProperty(obj,strPname));
            }
            catch(Exception excep)
            {
                throw new SQLException("Get value of " + strPname + " from instance of " + obj.getClass().getName()
                                       + " error: " + excep.getMessage());
            }
        }
        return this.execute();
    }

    public boolean containsColumn(String columnName)
        throws SQLException
    {
        if(Algorithms.isEmpty(columnName))
        {
            return false;
        }
        columnName = columnName.trim();
        for(String str : this.getColumnNames())
        {
            if(str.equalsIgnoreCase(columnName))
            {
                return true;
            }
        }
        return false;
    }

    public int indexOfColumn(String columnName)
        throws SQLException
    {
        if(Algorithms.isEmpty(columnName))
        {
            return -1;
        }
        columnName = columnName.trim();
        String[] names = this.getColumnNames();
        for(int n = 0;n < names.length;n++)
        {
            if(names[n].equalsIgnoreCase(columnName))
            {
                return n+1;//The begin-index of JDBC is 1, not 0, so we should add 1 to the result.
            }
        }
        return -1;
    }

} //CLASS END
